package com.dbutils;


import java.sql.Connection;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.comdev.vos.t_file;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.nutz.dao.Sqls;
import org.nutz.dao.pager.Pager;
import org.nutz.lang.Lang;
import org.nutz.lang.Mirror;


/**
 * jdbc -- 辅助类
 */
public class QueryHelper
{

    public static int threadCount = 0;
    public static boolean threadHasStart = false;

    private static QueryRunner run = new QueryRunner();


    /**
     * 执行INSERT/UPDATE/DELETE语句(删除最好别用该方法，执行会将数据库数据彻底删除)
     *
     * @param sql
     * @param params
     * @return
     */
    public static int execute(String sql, Object... params)
    {
        int out = 0;
        try
        {
            out = run.update(DBManager.getConnection(), sql, params);
        } catch (SQLException e)
        {
            DbUtils.printStackTrace(e);
            throw new RuntimeException(e);
        } finally
        {
            DBManager.closeConnection();
        }

        return out;
    }


    /**
     * 批量执行指定的SQL语句
     *
     * @return
     */
    public static int[] batch(String sql, Object[][] params)
    {
        int[] out = null;
        try
        {
            out = run.batch(DBManager.getConnection(), sql, params);
        } catch (SQLException e)
        {
            DbUtils.printStackTrace(e);
            throw new RuntimeException(e);
        } finally
        {
            DBManager.closeConnection();
        }
        return out;
    }


    /**
     * 第一条记录转换成Object数组
     *
     * @param sql
     * @param params
     * @return
     */
    public static Object[] query_Array(String sql, Object... params)
    {
        Object[] out = null;
        try
        {
            out = run.query(DBManager.getConnection(), sql, new ArrayHandler(),
                    params);
        } catch (SQLException e)
        {
            DbUtils.printStackTrace(e);
            throw new RuntimeException(e);
        } finally
        {
            DBManager.closeConnection();
        }
        return out;
    }

    /**
     * 返回sql中select的第一列
     *
     * @param sql
     * @param params
     * @return
     */
    public static List<Object> query_FirstColumnList(String sql, Object... params)
    {
        List<Object> out = null;
        try
        {
            out = (List) run.query(DBManager.getConnection(), sql, new ColumnListHandler(),
                    params);
        } catch (SQLException e)
        {
            DbUtils.printStackTrace(e);
            throw new RuntimeException(e);
        } finally
        {
            DBManager.closeConnection();
        }
        return out;
    }


    /**
     * 指定列的值为主键.value是一个map,map的k是数据库中字段名,v是对应的值
     * <br/>
     * 指定的列名不区分大小写,另外如果指定的列名对应的值重复,则用最后一个的值作为主键
     *
     * @param sql
     * @param params
     * @return
     */
    public static Object query_Keyed(String sql, String masterKeyName, Object... params)
    {
        Object out = null;
        try
        {
            out = run.query(DBManager.getConnection(), sql, new KeyedHandler(masterKeyName),
                    params);
        } catch (SQLException e)
        {
            DbUtils.printStackTrace(e);
            throw new RuntimeException(e);
        } finally
        {
            DBManager.closeConnection();
        }
        return out;
    }

    /**
     * 所有记录都转换成Object[],然后存放在List中
     *
     * @param sql
     * @param params
     * @return
     */
    public static List<Object[]> query_ArrayList(String sql, Object... params)
    {
        List<Object[]> out = null;
        try
        {
            out = run.query(DBManager.getConnection(), sql,
                    new ArrayListHandler(), params);
        } catch (SQLException e)
        {
            DbUtils.printStackTrace(e);
            throw new RuntimeException(e);
        } finally
        {
            DBManager.closeConnection();
        }
        return out;
    }


    /**
     * 查询第一条记录的T类型作为返回结果
     *
     * @param <T>
     * @param beanClass
     * @param sql
     * @param params
     * @return
     */
    public static <T> T query_Bean(Class<T> beanClass, String sql,
                                   Object... params)
    {
        T out = null;
        try
        {
            out = (T) run.query(DBManager.getConnection(), sql,
                    new BeanHandler<T>(beanClass), params);
        } catch (SQLException e)
        {
            DbUtils.printStackTrace(e);
            throw new RuntimeException(e);
        } finally
        {
            DBManager.closeConnection();
        }
        return out;
    }


    /**
     * 查询所有的记录的T类型
     *
     * @param <T>
     * @param beanClass
     * @param sql
     * @param params
     * @return
     */
    public static <T> List<T> query_BeanList(Class<T> beanClass, String sql,
                                             Object... params)
    {
        List<T> out = null;
        try
        {
            out = run.query(DBManager.getConnection(), sql,
                    new BeanListHandler<T>(beanClass), params);
        } catch (SQLException e)
        {
            DbUtils.printStackTrace(e);
            throw new RuntimeException(e);
        } finally
        {
            DBManager.closeConnection();
        }
        return out;
    }


    /**
     * 第一条记录作为一个Map
     *
     * @param sql
     * @param params
     * @return
     */
    public static Map<String, Object> query_Map(String sql, Object... params)
    {
        Map<String, Object> out = null;
        try
        {
            out = run.query(DBManager.getConnection(), sql, new MapHandler(),
                    params);
        } catch (SQLException e)
        {
            DbUtils.printStackTrace(e);
            throw new RuntimeException(e);
        } finally
        {
            DBManager.closeConnection();
        }
        return out;
    }


    /**
     * 每条记录作为一个Map
     *
     * @param sql
     * @param params
     * @return
     */
    public static List<Map<String, Object>> query_MapList(String sql,
                                                          Object... params)
    {
        List<Map<String, Object>> out = null;
        try
        {
            out = run.query(DBManager.getConnection(), sql,
                    new MapListHandler(), params);
        } catch (SQLException e)
        {
            DbUtils.printStackTrace(e);
            throw new RuntimeException(e);
        } finally
        {
            DBManager.closeConnection();
        }
        return out;
    }


    /**
     * 查询左上角的一条记录
     *
     * @param sql
     * @param params
     * @return
     */
    public static Object query_Scalar(String sql, Object... params)
    {
        Object out = null;
        try
        {
            out = run.query(DBManager.getConnection(), sql,
                    new ScalarHandler(), params);
        } catch (SQLException e)
        {
            DbUtils.printStackTrace(e);
            throw new RuntimeException(e);
        } finally
        {
            DBManager.closeConnection();
        }
        return out;
    }


    /**
     *
     *  返回list的数量
     *
     * @param sql
     * @param params
     * @return
     */
    public static int query_Count(String sql, Object... params)
    {
        int out = 0;
        try
        {

            List rs = run.query(DBManager.getConnection(), sql, new ArrayListHandler(),
                    params);
            out = rs.size();
            System.out.println("count:"+out+";"+sql);
        } catch (SQLException e)
        {
            DbUtils.printStackTrace(e);
            throw new RuntimeException(e);
        } finally
        {
            DBManager.closeConnection();
        }
        return out;
    }


    /**
     * 多个线程互不干扰，每个线程独立的数据库连接
     */
    public static void tx(Atom... atoms)
    {
        tx(Connection.TRANSACTION_READ_COMMITTED, atoms);
    }


    private static void tx(int level, Atom[] atoms)
    {
        if (null == atoms)
        {
            return;
        }
        int num = DBManager.getCount() == null ? 0 : DBManager.getCount();
        try
        {
            _begin(level);
            for (Atom atom : atoms)
            {
                atom.run();
            }
            _commit();
        } catch (Exception e)
        {
            _rollback(num);
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally
        {
            _depose();
        }
    }


    private static void _depose()
    {
        if (DBManager.getCount() == 0)
        {
            try
            {
                DBManager.getConn().close();
            } catch (Exception e)
            {
                e.printStackTrace();
            } finally
            {
                DBManager.setConn(null);
                DBManager.setCount(null);
            }
        }
    }


    private static void _rollback(int num)
    {
        DBManager.setCount(num);
        if (DBManager.getCount() == 0)// 最后一个事务提交后才能回滚，根本没有提交提交就进行回滚则出错
        {
            try
            {
                DBManager.getConn().rollback();
            } catch (Exception e)
            {
                e.printStackTrace();
            }
        }
    }


    private static void _commit()
    {
        DBManager.setCount(DBManager.getCount() - 1);
        if (DBManager.getCount() == 0)
        {
            try
            {
                DBManager.getConn().commit();
            } catch (Exception e)
            {
                e.printStackTrace();
            }
        }
    }


    private static void _begin(int level)
    {
        if (null == DBManager.getConn())
        {
            // 第一个连接要从数据源取得
            Connection conn;
            try
            {
                conn = DBManager.getDataSource().getConnection();
                conn.setTransactionIsolation(level);
                conn.setAutoCommit(false);// 打开事务
                DBManager.setConn(conn);
                DBManager.setCount(0);
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        DBManager.setCount(DBManager.getCount() + 1);
    }

    /**
     * 普通的vo对象 [类名就是表名]
     * [这里只用nutz的辅助类,不用NutDao]
     */
    public static <T> void insert(T obj)
    {
        Map<String, Object> map = Lang.obj2map(obj);

        String ks = "";
        String vs = "";

        for (Map.Entry entry : map.entrySet())
        {
            ks = ks.toString() + entry.getKey() + ",";
            vs = vs + Sqls.formatFieldValue(entry.getValue()) + ",";
        }


        ks = ks.substring(0, ks.length() - 1);
        vs = vs.substring(0, vs.length() - 1);

        String tableName = Mirror.me(obj).getType().getSimpleName();
        String sql = "INSERT INTO " + tableName + "  (" + ks + ")VALUES(" + vs + ")";
        System.out.println(sql);
        execute(sql);

    }

    /**
     * 更新vo对象,必须有objid字段,类名就是表名
     * [这里只用nutz的辅助类,不用NutDao]
     */
    public static <T> void update(T obj)
    {
        Map<String, Object> map = Lang.obj2map(obj);

        String ups = "";

        for (Map.Entry entry : map.entrySet())
        {
            ups = ups + entry.getKey() + "=" + Sqls.formatFieldValue(entry.getValue()) + ",";
        }
        ups = ups.substring(0, ups.length() - 1);

        String objid = Mirror.me(obj).getValue(obj, "objid").toString();

        String tableName = Mirror.me(obj).getType().getSimpleName();
        String sql = "UPDATE   " + tableName + " SET  " + ups + "    WHERE  objid='" + objid + "'";
        System.out.println(sql);
        execute(sql);

    }


    /**
     * 目前只支持mysql分页
     *
     * @param beanClass
     * @param sql
     * @param pager
     * @param <T>
     * @return
     */
    public static <T> List<T> query_BeanList_pager(Class<T> beanClass, String sql, Pager pager)
    {
        List<T> out = null;
        try
        {
            int m =  pager.getPageSize() * (pager.getPageNumber() - 1);
            int n = pager.getPageSize();
            sql = sql + " limit " + m + " ," + n + "";
            System.out.println(sql);
            out = run.query(DBManager.getConnection(), sql,
                    new BeanListHandler<T>(beanClass), new Object[]{});
        } catch (SQLException e)
        {
            DbUtils.printStackTrace(e);
            throw new RuntimeException(e);
        } finally
        {
            DBManager.closeConnection();
        }
        return out;


    }


    public static void main(String[] args)
    {

        t_file file=t_file.me();
        QueryHelper.insert(file);

        Pager pager = new Pager();
        pager.setPageNumber(2);
        pager.setPageSize(1);
        List<t_file> list = QueryHelper.query_BeanList(t_file.class, "select * from t_file where objid!='' ", pager);

        System.out.println(list);
    }

}
